package Controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import EntityBean.Organization;
import EntityBean.Premise;

public class ConnectDB {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	private ArrayList<Organization> listOrg;
	public void getConnection()
	{
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1130;databaseName=MockProject","sa" , "12345678");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public ArrayList<Organization> getListOrganizationsActive()
	{
		getConnection();
		listOrg = new ArrayList<Organization>();
		Organization org;
		try{
			ps = conn.prepareStatement("select * from Organizations where active='yes'");
			rs = ps.executeQuery();
			while(rs.next())
			{
				org = new Organization(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4),rs.getString(5));
				listOrg.add(org);
			}
			rs.close();
			ps.close();
			conn.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return listOrg;
	}
	
	public ArrayList<Premise> getListPremise(int id)
	{
		ArrayList<Premise> listPremise = new ArrayList<Premise>();;
		getConnection();
		Premise premise;
		String ac = String.valueOf(id);
		try{
			ps = conn.prepareStatement("select * from Premises join [Address] ON Premises.addressId = [Address].addressId AND id= '"+ac+"'");
			rs = ps.executeQuery();
			while(rs.next())
			{
				premise = new Premise(rs.getString(1),rs.getString(2), rs.getString(12), rs.getString(5), rs.getString(6));
				listPremise.add(premise);
			}
			rs.close();
			ps.close();
			conn.close();
		}catch(Exception e){
			
		}
		return listPremise;
	}
	public ArrayList< Premise> getOtherPremise(int id)
	{
		ArrayList<Premise> listPremise = new ArrayList<Premise>();;
		getConnection();
		Premise premise;
		String ac = String.valueOf(id);
		try{
			ps = conn.prepareStatement("select * from Premises join [Address] ON Premises.addressId = [Address].addressId AND id != '"+ac+"'");
			rs = ps.executeQuery();
			while(rs.next())
			{
				premise = new Premise(rs.getString(1),rs.getString(2), rs.getString(12), rs.getString(5), rs.getString(6));
				listPremise.add(premise);
			}
			rs.close();
			ps.close();
			conn.close();
		}catch(Exception e){
			
		}
		return listPremise;
	}
	public void deletePremise(String name)
	{
		getConnection();
		try{
			String query = "Delete From Premises WHERE premiseName = '"+ name +"'";
			ps = conn.prepareStatement(query);
		    ps.executeUpdate();
			
			System.out.println("deleted!");
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
//	public void UpdateRoles()
//	{
//		getConnection();
//		ps = conn.prepareStatement(")
//	}
}
